package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Deposit;

public class DepositDao {

	public DepositDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	//String pass = "webclass";

	// deposit 값 종류 불러오기
	public ArrayList<Deposit> getAllDeposit() throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Deposit> depositList = new ArrayList<Deposit>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con.prepareStatement("select * from deposit");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int depositid = rs.getInt("depositid");
				int deposit = rs.getInt("deposit");

				depositList.add(new Deposit(depositid, deposit));
			}

			return depositList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return depositList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// deposit id에 맞는 deposit value 가져오기
	public int getDepositValue(int depositId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int deposit = 0;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select deposit from deposit where depositId=?");
			pstmt.setInt(1, depositId);
			rs = pstmt.executeQuery();
			if (rs.next()) {

				deposit = rs.getInt("deposit");

			}

			return deposit;

		} catch (Exception ex) {
			ex.printStackTrace();
			return deposit;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// sid, uid에 맞는 deposit value 가져오기
	public int getDepositValueBysid(int sid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int depositValue = 0;
		try {

			con = DriverManager.getConnection(url, user, pass);

			pstmt = con
					.prepareStatement("select deposit from deposit,study where studyId=? and depositId=did");
			pstmt.setInt(1, sid);
			// pstmt.setInt(2, uid);
			pstmt.executeQuery();
			if (rs.next()) {
				depositValue = rs.getInt("deposit");
			}

			return depositValue;

		} catch (Exception ex) {
			ex.printStackTrace();
			return depositValue;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}
